﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using Pipes.Data;

namespace Pipes.Spreadsheet
{
  public class ExcelTableReader : IPipeInput<System.IO.Stream>, IPipeOutput<IDataRecord>
  {
    private int _startCol = 1;
    private int _startRow = 1;
    private int _endCol = 1;
    private int _endRow = 1;

    public bool FirstRowColumnNames { get; set; }
    public string Sheet { get; set; }
    private IEnumerable<System.IO.Stream> _source;

    public ExcelTableReader SetRange(int startCol, int startRow, int endCol, int endRow)
    {
      _startCol = startCol;
      _startRow = startRow;
      _endCol = endCol;
      _endRow = endRow;
      return this;
    }
    public ExcelTableReader SetSheet(string name)
    {
      this.Sheet = name;
      return this;
    }

    public void Initialize(IEnumerable<System.IO.Stream> source)
    {
      _source = source;
    }

    public IEnumerator<IDataRecord> GetEnumerator()
    {
      HSSFWorkbook workbook;
      System.Collections.IEnumerator rows;
      HSSFRow row;
      Cell cell;
      string[] columns = null;
      object[] values = null;
      int colCount = _endCol - _startCol + 1;

      foreach (var stream in _source)
      {
        workbook = new HSSFWorkbook(stream);
        rows = workbook.GetSheet(this.Sheet).GetRowEnumerator();
        
        while (rows.MoveNext())
        {
          row = (HSSFRow)rows.Current;

          // Keep looping until the start row is reached
          if ((row.RowNum + 1) >= _startRow)
          {
            // Ignore rows after the last row
            if ((row.RowNum + 1) > _endRow) yield break;

            if (columns == null && this.FirstRowColumnNames)
            {
              columns = new string[colCount];
              for (int i = _startCol - 1; i < Math.Min(row.LastCellNum, colCount + _startCol - 1); i++)
              {
                cell = row.GetCell(i);
                if (cell == null)
                {
                  columns[i - _startCol + 1] = string.Empty;
                }
                else
                {
                  columns[i - _startCol + 1] = cell.ToString();
                }
              }
            }
            else
            {
              values = new object[colCount];
              for (int i = _startCol - 1; i < Math.Min(row.LastCellNum, colCount + _startCol - 1); i++)
              {
                cell = row.GetCell(i);
                if (cell == null)
                {
                  values[i - _startCol + 1] = string.Empty;
                }
                else
                {
                  values[i - _startCol + 1] = cell.ToString();
                }
              }
              yield return new DataRecord(values, columns);
            }
          }
        }
      }
    }

    System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
    {
      return GetEnumerator();
    }
  }
}
